package com.kandinfo.base.common.util;


import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.persistence.Column;
import javax.persistence.Table;
import javax.sql.rowset.CachedRowSet;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.kandinfo.base.common.dto.EntityColumnBean;


/**
 * 简单实现一种利用反射自动封装JavaBean的JDBC查询
 * 
 * @author 琪伟
 *
 */
public class JdbcBeanUtil {

	private final static Logger LOG =  LoggerFactory.getLogger(JdbcBeanUtil.class);

	/**
	 * 通过Entity对象获取查询语句
	 * 
	 * @param obj
	 *            对象
	 * @return SQL
	 */
	public static String getSQLOfSelect(Object obj) {
		Class<? extends Object> objClass = obj.getClass();
		//获取表名
		String tableName = getTableName(objClass);
		if (StringUtils.isEmpty(tableName)) {
			return null;
		}
		//处理字段
		StringBuffer strBuffer = new StringBuffer("SELECT ");
		Field[] fields = getFields(objClass);
		for (Field field : fields) {
			String columnName = getColumnName(field,obj);
			if (StringUtils.isNotEmpty(columnName)) {
				strBuffer.append(columnName);
				strBuffer.append(",");
			}
		}
		strBuffer.delete(strBuffer.length() - 1, strBuffer.length());
		strBuffer.append(" FROM " );
		strBuffer.append(tableName);
		LOG.info(strBuffer.toString());
		return strBuffer.toString();
	}

	/**
	 * 通过Entity对象获取插入时的SQL
	 * 
	 * @param obj
	 *            java对象
	 * @return SQL
	 */
	public static String getSqlOfInsertOne(Object obj) {
		Class<? extends Object> objClass = obj.getClass();
		//获取表名
		String tableName = getTableName(objClass);
		if (StringUtils.isEmpty(tableName)) {
			return null;
		}
		String strSQL = "INSERT INTO " + tableName + "(#COLS) VALUES (#VALS)";
		Field[] fields = getFields(objClass);
		StringBuffer cols = new StringBuffer("");
		StringBuffer values = new StringBuffer("");

		for (Field field : fields) {
			if (field.getName().indexOf("serialVersionUID") > -1 ) {
				continue;
			}
			String methodName = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
			try {
				Method method = objClass.getMethod(methodName);
				Object val;
				val = method.invoke(obj);
				if (null != val) {
					Column column = field.getAnnotation(Column.class);
					if (column != null) {
						String columnName =  column.name();
						if (StringUtils.isNotBlank(columnName)) {
							cols.append(columnName);
							cols.append(",");
							if (val instanceof Integer || val instanceof Long || val instanceof Double) {
								values.append(val.toString());
								values.append(",");
							} else if (val instanceof String) {
								values.append("'" + val + "',");
							} else if (val instanceof java.util.Date) {
								String datestr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((java.util.Date) val);
								values.append("'" + datestr + "',");
							} else {
								values.append("'" + val.toString() + "',");
							}
						}
					}
				}
			} catch (NoSuchMethodException | SecurityException e) {
				LOG.error(e.toString());
				continue;
			} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
				LOG.error(e.toString());
			}
		}
		if (cols.length() > 1 && values.length() > 1) {
			cols.delete(cols.length() - 1, cols.length());
			values.delete(values.length() - 1, values.length());
			strSQL = strSQL.replace("#COLS", cols).replace("#VALS", values) +";";
		} else {
			LOG.error("警告：空对象无法完成操作啊");
			return null;
		}
		LOG.info(strSQL);
		return strSQL;
	}
	
	/**
	 * 通过Entity对象集合获取插入时的SQL
	 * 
	 * @param objList
	 *            java对象集合
	 * @return SQL
	 */
	public static Map<Integer, Map<Integer, Object>> getInsertSqlByList(List<?> objList) {
		Map<Integer, Map<Integer, Object>> map = new HashMap<Integer, Map<Integer, Object>>();
		Map<Integer, Object> sqlMap = new HashMap<Integer, Object>();
		Map<Integer, Object> paraMap = new HashMap<Integer, Object>();
		map.put(0, sqlMap);
		map.put(1, paraMap);
		if (CollectionUtils.isEmpty(objList)) {
			//对象为空返回空对象
			return map;
		}
		Class<? extends Object> parentClazz = objList.get(0).getClass();
		String tableName = getTableName(parentClazz);
		if (StringUtils.isEmpty(tableName)) {
			//表名不存在
			return map;
		}
		Map<Method, EntityColumnBean> helpMap = getMethodHelpMap(parentClazz);
		if (helpMap.size() < 1) {
			//该对象中无对应数据库中子段
			return map;
		}
		Set<Method> getMethodSet = helpMap.keySet();
		//业务逻辑数据准备
		String strSQL = "INSERT INTO " + tableName + "(#COLS) VALUES #VALS";
		StringBuffer cols = new StringBuffer("");
		StringBuffer values = new StringBuffer("");
		boolean firstObjectFlag = true;
		try {
			int paraIndex = 0;
			for (Object object : objList) {
				values.append(cols.toString().isEmpty() ? "" : ",").append("(");
				int index = 0;
				for (Method getMethod : getMethodSet) {
					if (getMethod.getName().indexOf("serialVersionUID") > 0 ) {
						continue;
					}
					EntityColumnBean entityColumnBean = helpMap.get(getMethod);
					if (firstObjectFlag) {
						cols.append(index > 0 ? "," : "").append(entityColumnBean.getColumnNameDB());
					}
					values.append(index > 0 ? "," : "").append("?");
					paraMap.put(++paraIndex, getMethod.invoke(object));
					index++;
				}
				values.append(")");
				firstObjectFlag = false;
			}
		} catch (Exception e) {
			LOG.error(e.toString(), e);
			return map;
		}
		if (cols.length() > 1 && values.length() > 1) {
			strSQL = strSQL.replace("#COLS", cols).replace("#VALS", values) + ";";
			sqlMap.put(0, strSQL);
		} else {
			LOG.error("警告：空对象无法完成操作啊");
			return map;
		}
		LOG.info(strSQL);
		return map;
	}
	
	
	public static Map<Integer, Map<Integer, Object>> getInsertSqlByList(List<?> objList,String tableName) {
		Map<Integer, Map<Integer, Object>> map = new HashMap<Integer, Map<Integer, Object>>();
		Map<Integer, Object> sqlMap = new HashMap<Integer, Object>();
		Map<Integer, Object> paraMap = new HashMap<Integer, Object>();
		map.put(0, sqlMap);
		map.put(1, paraMap);
		if (CollectionUtils.isEmpty(objList)) {
			//对象为空返回空对象
			return map;
		}
		Class<? extends Object> parentClazz = objList.get(0).getClass();
		if (StringUtils.isEmpty(tableName)) {
			//表名不存在
			return map;
		}
		Map<Method, EntityColumnBean> helpMap = getMethodHelpMap(parentClazz);
		if (helpMap.size() < 1) {
			//该对象中无对应数据库中子段
			return map;
		}
		Set<Method> getMethodSet = helpMap.keySet();
		//业务逻辑数据准备
		String strSQL = "INSERT INTO " + tableName + "(#COLS) VALUES #VALS";
		StringBuffer cols = new StringBuffer("");
		StringBuffer values = new StringBuffer("");
		boolean firstObjectFlag = true;
		try {
			int paraIndex = 0;
			for (Object object : objList) {
				values.append(cols.toString().isEmpty() ? "" : ",").append("(");
				int index = 0;
				for (Method getMethod : getMethodSet) {
					if (getMethod.getName().indexOf("serialVersionUID") > 0 ) {
						continue;
					}
					EntityColumnBean entityColumnBean = helpMap.get(getMethod);
					if (firstObjectFlag) {
						cols.append(index > 0 ? "," : "").append(entityColumnBean.getColumnNameDB());
					}
					values.append(index > 0 ? "," : "").append("?");
					paraMap.put(++paraIndex, getMethod.invoke(object));
					index++;
				}
				values.append(")");
				firstObjectFlag = false;
			}
		} catch (Exception e) {
			LOG.error(e.toString(), e);
			return map;
		}
		if (cols.length() > 1 && values.length() > 1) {
			strSQL = strSQL.replace("#COLS", cols).replace("#VALS", values) + ";";
			sqlMap.put(0, strSQL);
		} else {
			LOG.error("警告：空对象无法完成操作啊");
			return map;
		}
		return map;
	}
	
	
	public static String getUpdateSql(Object entity){
		Class<? extends Object> clazz = entity.getClass();
		//获取表名
		String tableName = JdbcBeanUtil.getTableName(clazz);
		if (StringUtils.isEmpty(tableName)) {
			String msg = "表名为空["+clazz+"]";
			LOG.debug(msg);
			return null;
		}
		
		Map<Method, EntityColumnBean> helpMap = JdbcBeanUtil.getMethodHelpMap(clazz);
		if (MapUtils.isEmpty(helpMap)) {
			LOG.debug("该对象中无对应数据库中子段["+clazz+"]");
			return null;
		}
		Object id;
		try {
			Method getIdMethod = clazz.getMethod("getId");
			id = getIdMethod.invoke(entity);
			if (id == null) {
				LOG.debug("主键为空！");
				return null;
			}
		} catch (NoSuchMethodException | SecurityException e) {
			LOG.error(e.toString(),e);
			return null;
		} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
			LOG.error(e.toString(),e);
			return null;
		}
		
		Set<Method> getMethodSet = helpMap.keySet();
		String strSQL = "UPDATE " + tableName + " SET #VALS #WHERE";
		StringBuffer values = new StringBuffer("");
		try {
			for (Method method : getMethodSet) {
				if (("getId").equals(method.getName())) {
					continue;
				}
				Object val = method.invoke(entity);
				if (val == null) {
					continue;
				}
				if (values.length() > 0 ) {
					values.append(" , ");
				}
				values.append(helpMap.get(method).getColumnNameDB() + " = ");
				if (val instanceof Integer || val instanceof Long || val instanceof Double) {
					values.append(val.toString());
				} else if (val instanceof String) {
					values.append("'" + val + "' ");
				} else if (val instanceof java.util.Date) {
					String datestr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((java.util.Date) val);
					values.append("'" + datestr + "' ");
				} else {
					values.append("'" + val.toString() + "' ");
				}
			}
		} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
			LOG.error(e.toString());
		}
		strSQL = strSQL.replace("#WHERE", " WHERE id = " + id + " ");
		strSQL = strSQL.replace("#VALS", values);
		LOG.debug(" SQL: " + strSQL);
		return strSQL;
	}
	
	/**
	 * 通过Entity对象集合获取插入时的SQL
	 * 
	 * @param objList
	 *            java对象集合
	 * @return SQL
	 */
	public static String getSqlOfInsertList(List<?> objList) {
		Class<? extends Object> parentClazz = objList.get(0).getClass();
		String tableName = getTableName(parentClazz);
		return getSqlOfInsertList(objList,tableName);
	}
	
	/**
	 * 通过Entity对象集合获取插入时的SQL
	 * 
	 * @param objList
	 *            java对象集合
	 * @param tableName
	 * 			  表名
	 * @return SQL
	 */
	public static String getSqlOfInsertList(List<?> objList,String tableName) {
		
		if (CollectionUtils.isEmpty(objList)) {
			//对象为空返回空对象
			return null;
		}
		if (StringUtils.isEmpty(tableName)) {
			//表名不存在
			return null;
		}
		Class<? extends Object> parentClazz = objList.get(0).getClass();
		Map<Method, EntityColumnBean> helpMap = getMethodHelpMap(parentClazz);
		if (helpMap.size() < 1) {
			//该对象中无对应数据库中子段
			return null;
		}
		Set<Method> getMethodSet = helpMap.keySet();
		//业务逻辑数据准备
		String strSQL = "INSERT INTO " + tableName + "(#COLS) VALUES #VALS";
		StringBuffer cols = new StringBuffer("");
		StringBuffer values = new StringBuffer("");
		boolean firstObjectFlag = true;
		
		try {
			for (Object object : objList) {
				values.append("(");
				for (Method getMethod : getMethodSet) {
					EntityColumnBean entityColumnBean = helpMap.get(getMethod);
					//deal value
					Object val = getMethod.invoke(object);
					Class<? extends Object> columnClazz = entityColumnBean.getClazz();
					if (val != null) {
						if (val instanceof Integer || val instanceof Long || val instanceof Double) {
							values.append(val.toString());
							values.append(",");
						} else if (val instanceof String) {
							values.append("'" + val + "',");
						} else if (val instanceof java.util.Date) {
							String datestr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((java.util.Date) val);
							values.append("'" + datestr + "',");
						} else {
							values.append("'" + val.toString() + "',");
						}
					}else{
						//空处理
						if (columnClazz == String.class) {
							values.append("'',");
						}else{
							values.append("null,");
						}
					}
					//deal column
					if (firstObjectFlag) {
						cols.append(entityColumnBean.getColumnNameDB());
						cols.append(",");
					}
				}
				values.delete(values.length() - 1, values.length());
				values.append("),");
				firstObjectFlag = false;
			}
		} catch (Exception e) {
			LOG.error("",e);
			return null;
		}
		if (cols.length() > 1 && values.length() > 1) {
			cols.delete(cols.length() - 1, cols.length());
			values.delete(values.length() - 1, values.length());
			strSQL = strSQL.replace("#COLS", cols).replace("#VALS", values) + ";";
		} else {
			LOG.error("警告：空对象无法完成操作啊");
			return null;
		}
		LOG.info(strSQL);
		return strSQL;
	}
	
	
	
	/**
	 * 获得entity getMethod 和 对应数据库中字段属性 
	 * 
	 * @param clazz
	 * @return
	 */
	public static Map<Method, EntityColumnBean> getMethodHelpMap(Class<? extends Object> parentClazz){
		Map<Method, EntityColumnBean> helpMap = new HashMap<Method, EntityColumnBean>();
		Field[] childFields = getFields(parentClazz);
		for (Field field : childFields) {
			if (field.getName().indexOf("serialVersionUID") > -1 ) {
				continue;
			}
			EntityColumnBean entityColumnBean = new EntityColumnBean();
			String methodName = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
			try {
				Method getMethod = parentClazz.getMethod(methodName);
				Column column = field.getAnnotation(Column.class);
				if (column != null) {
					String columnName =  column.name();
					entityColumnBean.setParentClazz(parentClazz);
					entityColumnBean.setColumnNameDB(columnName);
					entityColumnBean.setColumnName(field.getName());
					entityColumnBean.setClazz(field.getType());
					entityColumnBean.setGetMethod(getMethod);
					helpMap.put(getMethod, entityColumnBean);
				}
			} catch (NoSuchMethodException e) {
				LOG.error(e.toString());
				continue;
			} catch (SecurityException e) {
				LOG.error(e.toString());
			}
		}
		return helpMap;
	}
	
	/**
	 * 获得entity和极其父类的Field[]
	 * 
	 * @param clazz
	 * @return
	 */
	private static Field[] getFields(Class<? extends Object> clazz){
		Field[] childFields = clazz.getDeclaredFields();
		Class<? extends Object> superClass = clazz.getSuperclass();
		if (!superClass.getName().contains("BaseDomain")) {
			return childFields;
		}
		Field[] superFields =superClass.getDeclaredFields();
		return arrayCopyField(childFields,superFields);
	}
	public static Field[] arrayCopyField(Field[] a,Field[] b){
		Field[] c= new Field[a.length+b.length];  
	    System.arraycopy(a, 0, c, 0, a.length);  
	    System.arraycopy(b, 0, c, a.length, b.length);
	    return c;
	}
	
	/**
	 *  获取字段对应注解name，即数据库中的name
	 * 
	 * @param field
	 * @param obj 
	 * @return
	 */
	private static String getColumnName(Field field, Object obj) {
		Class<? extends Object> objClass = obj.getClass();
		if (field.getName().indexOf("serialVersionUID") > -1 ) {
			return null;
		}
		String methodName = field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
		try {
			Method method = objClass.getMethod("get" + methodName);
			if (null != method) {
				Object val =  method.invoke(obj);
				if (val != null) {
					Column column = field.getAnnotation(Column.class);
					if (column != null) {
						return column.name();
					}
				}
			}
		} catch (Exception e) {
			LOG.error(e.toString());
		} 
		return null;
	}

	/**
	 * 获取表名
	 * 
	 * @param objClass
	 * @return
	 */
	public static String getTableName(Class<? extends Object> objClass) {
		Table table = objClass.getAnnotation(Table.class);
		if (table == null) {
			return null;
		}
		return table.name();
	}

	/**
	 * 通过缓存结果集构造对象列表
	 * 
	 * @param clazz
	 *            javaBean类
	 * @param crs
	 *            缓存结果集
	 * @return 对象列表
	 */
	public static <T> List<T> getBeanFormCacheRowSet(Class<?> clazz, CachedRowSet crs) {
		Map<String, String> helpMap = getHelpMap(clazz);
		List<T> resultList = new ArrayList<T>();
		try {
			ResultSetMetaData md = crs.getMetaData();
			int columnCount = md.getColumnCount();
			T object = null; 
			while (crs.next()) {
				for (int i = 1; i <= columnCount; i++) {
					if (crs.getObject(i) == null) {
						continue;
					}
					String classNameDB = md.getColumnClassName(i);
					String className = getClassName(classNameDB);
					String filedName = helpMap.get(md.getColumnName(i));
					if (StringUtils.isBlank(filedName)) {
						continue;
					}
					String methodName = "set" + filedName.substring(0, 1).toUpperCase()
							+ filedName.substring(1);
					Object objVal = crs.getObject(i);
					setObjectVal(className, objVal, clazz, methodName, object);
				}
				resultList.add(object);
			}
		} catch (Exception e) {
			LOG.error(e.toString());
			return new ArrayList<T>();
		}
		return resultList;
	}

	
	/**
	 * 通过ResultSet结果集构造对象列表
	 * 
	 * @param clazz
	 *            javaBean类
	 * @param resultSet
	 *            ResultSet
	 * @return 对象列表
	 */
	public static <T> List<T> getBeanFormResultSet(Class<T> clazz, ResultSet resultSet) {
		Map<String, String> helpMap = getHelpMap(clazz);
		List<T> resultList = new ArrayList<T>();
		try {
			ResultSetMetaData md = resultSet.getMetaData();
			int columnCount = md.getColumnCount();
			T object = null;
			while (resultSet.next()) {
				object = clazz.newInstance();
				for (int i = 1; i <= columnCount; i++) {
					if (resultSet.getObject(i) == null) {
						continue;
					}
					String classNameDB = md.getColumnClassName(i);
					String className = getClassName(classNameDB);
					String filedName = helpMap.get(md.getColumnName(i));
					if (StringUtils.isBlank(filedName)) {
						continue;
					}
					String methodName = "set" + filedName.substring(0, 1).toUpperCase()
							+ filedName.substring(1);
					Object objVal = resultSet.getObject(i);
					setObjectVal(className, objVal, clazz, methodName, object);
				}
				resultList.add(object);
			}
		} catch (Exception e) {
			LOG.error(e.toString(),e);
			return new ArrayList<T>();
		}
		return resultList;
	}
	
	/**
	 * 赋值
	 * 
	 * @param className
	 * @param objVal
	 * @param clazz
	 * @param methodName
	 * @param object
	 */
	private static void setObjectVal(String className,Object objVal,Class<?> clazz,String methodName,Object object){
		try {
			if("setSerialNumber".equals(methodName)) {
				return ;
			}
			if ("java.math.BigInteger".equals(className)) {
				Long valLong = Long.valueOf(objVal.toString());
				clazz.getMethod(methodName, Class.forName("java.lang.Long")).invoke(object, valLong);
			}else{
				clazz.getMethod(methodName, Class.forName(className)).invoke(object, objVal);
			}
		} catch (NoSuchMethodException e) {
//			LOG.error("特殊类型字段导致表字段与JavaBean属性不对应！没有找到参数类型是[" + className + "]的SETER方法：" + methodName + "  MSG:"+ e.toString());
		} catch (Exception e) {
			LOG.error(e.toString());
		}
	}
	
	/**
	 * 
	 * className DB->JAVA
	 * 
	 * @param className
	 * @return
	 */
	private static String getClassName(String classNameDB){
		String className = "";
		if ("java.sql.Timestamp".equals(classNameDB) || "java.sql.Date".equals(classNameDB)) {
			className = "java.util.Date";
		} else if ("java.sql.Time".equals(classNameDB)) {
			className = "java.lang.String";
		} else if ("java.lang.Byte".equals(classNameDB) || "java.lang.Short".equals(classNameDB)) {
			className = "java.lang.Integer";
		} else {
			className = classNameDB;
		}
		return className;
	}
	
	/**
	 * 获得Entity对象中字段的map<className DB,className JAVA>
	 * 
	 * @param clazz
	 * @return
	 */
	private static Map<String, String> getHelpMap(Class<?> clazz){
		Map<String, String> map = new HashMap<>();
		Field[] fields = getFields(clazz);
		for (Field field : fields) {
			Column column = field.getAnnotation(Column.class);
			if (column != null) {
				String columnName =  column.name();
				if (StringUtils.isNotBlank(columnName)) {
					map.put(columnName, field.getName());
				}
			}
		}
		return map;
	}
	
	/**
	 * PreparedStatement 赋值
	 * 
	 * @param statement
	 * @param index
	 * @param object
	 * @return
	 */
	public static PreparedStatement setPreparedStatementVal(PreparedStatement statement,Integer index,Object object){
		try {
			if (object instanceof String) {
				statement.setString(index, object.toString());
			} else if (object instanceof Integer) {
				statement.setInt(index, (int)object);
			} else if (object instanceof Long) {
				statement.setLong(index, (Long)object);
			} else if (object instanceof Date || object instanceof java.util.Date) {
				Timestamp time = new Timestamp(((Date) object).getTime());
				statement.setTimestamp(index, time);
			} else if (object instanceof Double) {
				statement.setDouble(index, (Double)object);
			} else if (object instanceof Float) {
				statement.setFloat(index, (Float)object);
			} else {
				statement.setObject(index, object);
			}
		} catch (SQLException e) {
			LOG.error("jdbc PreparedStatement set exception", e);
		}
		return statement;
	}
}
